﻿using System;
using System.Collections.Generic;
using System.Text;
using DbView;

/*
	Todo:
		- scripting schema and data
		 - 
		- add / remove / modify fields
		- purge the code
		- upgrade table from 3.5 to 4.0
		Investigate deployment and versions (3.5 / 4.0)
		- final run through document and commit.
		- upgrade table from 3.5 to 4.0
			- error trapping
		- new database. Set password quietly
	Documentation
	- changing drivers.
	- 
*/

namespace SqlCeDriver
{
	class SqlCeTemplates
	{
		static SqlCeTemplates g_source = new SqlCeTemplates();
		public static SqlCeTemplates Templates { get { return g_source; } }

		SqlCeTemplates()
		{
			this.Initialise();
		}

		void Initialise()
		{
			this.templates = new Dictionary<int,Dictionary<Commands.Cmnd,string>>();
			this.templates.Add(3, new Dictionary<Commands.Cmnd,string>()); // SqlLite3
			// populate
			Dictionary<Commands.Cmnd, String> t = this.templates[3];
			t.Add(Commands.Cmnd.Tables, TABLES_SQLTEMPLATE);
			t.Add(Commands.Cmnd.Fields, FIELDS_SQLTEMPLATE);
			t.Add(Commands.Cmnd.Aggregate, AGGREGATE_SQLTEMPLATE);
			t.Add(Commands.Cmnd.RowCount, COUNT_SQLTEMPLATE);
			t.Add(Commands.Cmnd.MinMax, MINMAX_SQLTEMPLATE);
			t.Add(Commands.Cmnd.Average, AVG_SQLTEMPLATE);
			t.Add(Commands.Cmnd.Indexes, INDEX_SQLTEMPLATE);
			t.Add(Commands.Cmnd.ForeignKeys, FK_SQLTEMPLATE);
			t.Add(Commands.Cmnd.TableFieldSearch, TABLEFIELD_SEARCH_SQLTEMPLATE);
		}
		
		public String FetchTemplate(int sqlVersion, Commands.Cmnd command)
		{
			if (this.templates[sqlVersion].ContainsKey(command))
				return this.templates[sqlVersion][command];
			return ""; // can't help
		}

		// process [[ ]] filter clause
		// 
		public String ProcessFilterClause(String clause, String filterText, bool isCS)
		{
			int pos = clause.IndexOf("[[");
			// little extra twist here caused by the need for the SQL CE engine to execute
			// scripts one statement at a time. After settling on a blank line as a statement breaker
			// found that some standard commands will cause a blank line when there is not filter text.
			// below implements a strategy to remove the blank line in these cases.
			String seek = "]]\r\n";
			int pos2 = clause.IndexOf(seek);
			if (pos2 == -1)
			{
				seek = "]]";
				pos2 = clause.IndexOf(seek);
			}
			// if [[ ]] not present nothing to do
			if (pos == -1 || pos2 == -1 || pos2 < pos) return clause;
			
			// There is filter text to process.
			pos2 += seek.Length; // advance to end
			String filterClause = clause.Substring(pos, (pos2 - pos));
			if (filterText.Length == 0)
			{
				return this.ProcessFilterClause(clause.Replace(filterClause, ""), filterText, isCS);
			}
			// make comparison text u/c if database is case sensitive.
			String usedFilterText = (isCS) ? filterText.ToUpper() : filterText;
			// Check filter text for % and remove default
			if (filterText.IndexOf('%') != -1)
				filterClause = filterClause.Replace("'%%'", "'"+usedFilterText+"'");
			else
				filterClause = filterClause.Replace("'%%'", "'%"+usedFilterText+"%'");
			// check clause for _ and remove REPLACE()
			if (filterText.IndexOf("_") != -1)
			{
				filterClause = filterClause.Replace("REPLACE(","")    //
				               .Replace(",'_','')", "");  //
			}
			// if case insensitive remove UPPER()
			if (!isCS)
			{
				filterClause = filterClause.Replace("UPPER(","")    //
				               .Replace(") like", " like");  //
			}
			
			String x = clause.Substring(0, pos) + 
			           filterClause.Replace("[[","").Replace("]]", "") + 
			           clause.Substring(pos2);
			
			// process more than one clause
			return this.ProcessFilterClause(x, filterText, isCS);
		}

		Dictionary<int, Dictionary<Commands.Cmnd, String>> templates;

		////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		// Templates from here on
		////////////////////////////////////////////////////////////////////////////////////////////////////////////////

		const String TABLES_SQLTEMPLATE = @"
  select TABLE_NAME from INFORMATION_SCHEMA.TABLES
   [[ where TABLE_NAME like '%%' ]]
order by TABLE_NAME";

		const String VIEWS_SQLTEMPLATE = @"
";
		const String FIELDS_SQLTEMPLATE = @"
select  cols.COLUMN_NAME as ColumnName,
		cols.DATA_TYPE as [Type],
		convert(nvarchar(20), case when cols.DATA_TYPE in ('numeric')
									then cols.NUMERIC_PRECISION 
									else cols.CHARACTER_MAXIMUM_LENGTH end) as [Length],
		case when cols.DATA_TYPE in ('numeric')
				then cols.NUMERIC_SCALE 
				else NULL end as [Dp],
		cols.IS_NULLABLE as [Nulls?],
		REPLACE(REPLACE('(%s,%i)','%s',AUTOINC_SEED),'%i',AUTOINC_INCREMENT) as [Auto?],
		case when i.is_primary_key = 1 then 'YES' else NULL end as [PKey?],
		case when fk.is_fk = 1 then 'YES' else NULL end as [FKey?],
		case when i.is_non_pk_index = 0 then 'YES' else NULL end as [Index?],
		cols.COLUMN_DEFAULT as [Default]
  from INFORMATION_SCHEMA.COLUMNS cols
left join (select i.TABLE_NAME, i.COLUMN_NAME, 
					max(cast(PRIMARY_KEY as tinyint)) as is_primary_key, 
					min(cast(PRIMARY_KEY as tinyint)) as is_non_pk_index
			  from INFORMATION_SCHEMA.COLUMNS cols
			  join INFORMATION_SCHEMA.INDEXES i on i.TABLE_NAME = cols.TABLE_NAME and i.COLUMN_NAME = cols.COLUMN_NAME
			group by i.TABLE_NAME, i.COLUMN_NAME 
		 ) i on i.TABLE_NAME = cols.TABLE_NAME and i.COLUMN_NAME = cols.COLUMN_NAME
left join (select distinct u.TABLE_NAME, u.COLUMN_NAME, 1 as is_FK
			  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
			  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
			 where c.CONSTRAINT_TYPE = 'FOREIGN KEY'
		   ) fk on fk.TABLE_NAME = cols.TABLE_NAME and fk.COLUMN_NAME = cols.COLUMN_NAME
 where cols.TABLE_NAME = '{0}'
   and (cols.COLUMN_NAME like '%{1}%')
 order by cols.ORDINAL_POSITION";

		const String AGGREGATE_SQLTEMPLATE = @"
select top ({4}) [{0}], [COUNT] from
(
select 
    [{0}] as [{0}], 
    count([{0}]) as [COUNT] 
from ( select [{0}] as [{0}] 
       from {1}
      where UPPER([{0}]) like '%{2}%' {3}
     ) sq 
group by [{0}] 
) sq
order by [COUNT] desc,[{0}]
";
		const String COUNT_SQLTEMPLATE = @"
        select '{2}' as COUNT_{0}, 
               count([{0}]) as [COUNT] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";

		const String MINMAX_SQLTEMPLATE = @"
        select '{2}' as MINMAX_{0}, 
               min([{0}]) as [MIN], 
               max([{0}]) as [MAX] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";

		const String AVG_SQLTEMPLATE = @"
        select '{2}' as SUM_{0}, 
               sum(cast ([{0}] as float)) as [SUM], 
               avg(cast([{0}] as float)) as [AVERAGE] from {1} 
         where UPPER([{0}]) like '%{2}%' {3}
        ";
		const String INDEX_SQLTEMPLATE = @"
select INDEX_NAME,
		TABLE_NAME,
       COLUMN_NAME,
	   PRIMARY_KEY
  from INFORMATION_SCHEMA.INDEXES
[[ where TABLE_NAME like '%%'
      or COLUMN_NAME like '%%' ]]
order by TABLE_NAME, 1-PRIMARY_KEY, ORDINAL_POSITION
";

		const String FK_SQLTEMPLATE = @"
select 
	fk.CONSTRAINT_TABLE_NAME as FK_TABLE,
    c.COLUMN_NAME as FK_COLUMN,
	fk.UNIQUE_CONSTRAINT_TABLE_NAME as PK_TABLE,
    c1.COLUMN_NAME as PK_COLUMN,
	fk.CONSTRAINT_NAME as FK_NAME,
	fk.DELETE_RULE,
	fk.UPDATE_RULE
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c1 on c1.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME
[[ where CONSTRAINT_TABLE_NAME like '%%'
   and (UNIQUE_CONSTRAINT_TABLE_NAME like '%%') ]]
";

		const String TABLEFIELD_SEARCH_SQLTEMPLATE = @"
select cols.TABLE_NAME, cols.COLUMN_NAME
  from INFORMATION_SCHEMA.COLUMNS cols
 where cols.COLUMN_NAME like '%{0}%'
[[ and (cols.COLUMN_NAME like '%%' 
      or cols.TABLE_NAME like '%%') ]];
";
	}
}
